package com.zhang.crawler.utils;
import com.zhang.crawler.book.DoubanBookEntity;
import com.zhang.crawler.movietv.DoubanEntity;

import java.sql.*;
import java.util.List;

public class JdbcUtil {

    //开启连接
    public static Connection getConn() {

        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/movietv?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&rewriteBatchedStatements=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useSSL=false";
        String username = "root";
        String password = "root";
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void insert(List<DoubanEntity> entityList) throws Exception {
        Connection conn = getConn();
        conn.setAutoCommit(false);
        int count = 0;
        //拼接处理insert 语句
        String sql = "insert into movie_msg " +
                "(id, douban_id, title,play_able, rate, url, cover,is_new, episodes_count, " +
                "star, sub_type, directors, actors, duration, region, types ,release_year)" +
                " values (null, ?, ?,?, ?, ?, ?,?,?,?,?,?,?,?,?,?,? )";
        insertMySql(conn,entityList,sql);  //mysql插入


    }

    public static void insertMySql(Connection conn, List<DoubanEntity> entityList,String sql) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            //循环赋值 插入数据
            for (DoubanEntity entity :entityList){
                System.out.println(entity);
                String doubanId = entity.getDoubanId();
                boolean query = JdbcUtil.query(doubanId,conn);
                if(!query){
                    continue;
                }
                pstmt.setString(1,entity.getDoubanId());
                pstmt.setString(2,entity.getTitle());
                pstmt.setBoolean(3,entity.getPlayAble());
                pstmt.setString(4,entity.getRate());
                pstmt.setString(5,entity.getUrl());
                pstmt.setString(6,entity.getCover());
                pstmt.setBoolean(7,entity.getIsNew());
                pstmt.setString(8,entity.getEpisodesCount());
                pstmt.setDouble(9,entity.getStar());
                pstmt.setString(10,entity.getSubType());
                pstmt.setString(11,entity.getDirectors());
                pstmt.setString(12,entity.getActors());
                pstmt.setString(13,entity.getDuration());
                pstmt.setString(14,entity.getRegion());
                pstmt.setString(15,entity.getTypes());
                pstmt.setString(16,entity.getReleaseYear());
                pstmt.addBatch();
            }
            //执行插入语句
            pstmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static void insert2(List<DoubanBookEntity> entityList) throws Exception {
        Connection conn = getConn();
        conn.setAutoCommit(false);
        int count = 0;
        //拼接处理insert 语句
        String sql = "insert into douban_book " +
                "(id, title, pic_url, author, book_type, grade,translator, grade_num, ele_url, detail_url, price, " +
                "  press, publication,introductory)" +
                " values (null, ?, ?,?, ?, ?, ?,?,?,?,?,? ,?,?)";
        insertBook(conn,entityList,sql);  //mysql插入
    }

    private static void insertBook(Connection conn, List<DoubanBookEntity> entityList, String sql) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            //循环赋值 插入数据
            for (DoubanBookEntity entity :entityList){
                System.out.println(entity);
                String doubanId = entity.getTitle();
//                boolean query = JdbcUtil.query(doubanId,conn);
//                if(!query){
//                    continue;
//                }
                pstmt.setString(1,entity.getTitle());
                pstmt.setString(2,entity.getPicUrl());
                pstmt.setString(3,entity.getAuthor());
                pstmt.setString(4,entity.getBookType());
                pstmt.setString(5,entity.getGrade());
                pstmt.setString(6,entity.getTranslator());
                pstmt.setString(7,entity.getGradeNum());
                pstmt.setString(8,entity.getEleUrl());
                pstmt.setString(9,entity.getDetailUrl());
                pstmt.setString(10,entity.getPrice());
                pstmt.setString(11,entity.getPress());
                pstmt.setString(12,entity.getPublication());
                pstmt.setString(13,entity.getIntroductory());
                pstmt.addBatch();
            }
            //执行插入语句
            pstmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void createIncrement(String table){
        Connection conn = JdbcUtil.getConn();

    }

    /**
     * 判断数据库表是否存在
     * @param data          数据库名
     * @param tableName     表名
     * @return    true 存在  false 不存在
     */
    public static boolean tables(String data ,String tableName){
        boolean flag=false;
        Connection conn = JdbcUtil.getConn();
        try {
            DatabaseMetaData meta = conn.getMetaData();
            // 第一个参数catalog在MySQL中对应数据库名：michaeldemo
            ResultSet rsTables = meta.getTables(data, null, tableName,
                    new String[] { "TABLE" });

            // 第二个参数schemaPattern在ORACLE中对应用户名：ZHANG
//             ResultSet rsTables = meta.getTables(null, "ZHANG", tableName,
//             new String[] { "TABLE" });
            System.out.println("getTables查询信息如下：");
            while (rsTables.next()) {
                System.out.println("获取的表有    "+rsTables.getString("TABLE_NAME"));
                if(tableName.equals(rsTables.getString("TABLE_NAME"))){
                    flag=true;
                    break;
                }
            }
            rsTables.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

    /**
     * 插入数据
     * @param columnList  字段集合
     * @param dataList    数据集合
     * @param table       表名
     */
    public static int insert(List<String> columnList, List<List<String>> dataList, String table) throws SQLException {
        Connection conn = getConn();
        conn.setAutoCommit(false);
        int count = 0;
        //拼接处理insert 语句
        String s1 = "insert into " + table + " (data_up_uuid ,data_up_time,data_up_status ,";
        StringBuffer sb = new StringBuffer(s1);
        StringBuffer ssb = new StringBuffer("("+null+",now(),'I' ,");
        if (columnList != null && columnList.size() > 0) {
            for (String s : columnList) {
                sb.append(s).append(",");
                ssb.append("?").append(",");
            }
        }
        String valu = ssb.substring(0, ssb.lastIndexOf(",")) + ")";   //(?,?,?,?)
        String inse = sb.substring(0, sb.lastIndexOf(",")) + ") values";  //insert into test (id,name,password,phone) values
        StringBuffer sbb = new StringBuffer();
        String sql = sbb.append(inse).append(valu).toString(); //insert into test (id,name,password,phone) values(?,?,?,?)
        //插入语句
        System.out.println(sql);
        PreparedStatement pstmt = null;
        insertMySql(conn,sql,columnList,dataList);  //mysql插入
        //insertOracel(conn,sql,columnList,dataList);  //oracel插入
        System.out.println(table+"表插入成功"+count);
        return count;
    }

    //Oracel插入
    private static void insertOracel(Connection conn, String sql, List<String> columnList, List<List<String>> dataList) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            //循环赋值 插入数据
            for (int j = 0; j < dataList.size(); j++) {
                for (int i = 0; i < columnList.size(); i++) {
                    pstmt.setString(i + 1, dataList.get(j).get(i));
                }
                pstmt.addBatch();
                if(j%300==0){
                    pstmt.executeBatch();
                    conn.commit();
                    pstmt.clearBatch();
                }
            }
            //执行插入语句
            pstmt.executeBatch();
            conn.commit();
            pstmt.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //mysql插入
    public static void insertMySql(Connection conn,String sql ,List<String> columnList, List<List<String>> dataList){
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            //循环赋值 插入数据
            for (int j = 0; j < dataList.size(); j++) {
                for (int i = 0; i < columnList.size(); i++) {
                    pstmt.setString(i + 1, dataList.get(j).get(i));
                }
                pstmt.addBatch();
                if(j%300==0){
                    pstmt.executeBatch();
                    conn.commit();
                    pstmt.clearBatch();
                }
            }
            //执行插入语句
            pstmt.executeBatch();
            conn.commit();
            pstmt.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public static boolean query(String doubanId, Connection conn) {
        String sql = "select * from douban_book where `title=`"+doubanId;
        PreparedStatement pstmt;
        boolean flag=true;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                flag=false;
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    private static int update(String oldName, String newPass) {
        Connection conn = getConn();
        int i = 0;
        String sql = "update users set password='" + newPass
                + "' where username='" + oldName + "'";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    private static int delete(String username) {
        Connection conn = getConn();
        int i = 0;
        String sql = "delete users where username='" + username + "'";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //创建表
    public static void createTable(String sql) {
        try {
            Connection conn = getConn();
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            int count = preparedStatement.executeUpdate(sql);
            if (count == 0) {
                System.out.println("创建成功");
            } else {
                System.out.println("创建失败");
            }
            preparedStatement.close();
            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }


    public static void main(String[] args) throws SQLException {

//        String table="zhang123";
//        boolean bool=JdbcUtil.tables("test",table);
//        System.out.println(bool);
        //创建表
			String sql =  "CREATE TABLE Test_Table (" +
                    "ID number(11) primary key," +
                    "Name varchar(50) not null, " +
                    "Value1 varchar(50) not null " +
                    ")";
			createTable(sql);
        //插入
//        List<String> columnList = new ArrayList<>();
//        columnList.add("id");
//        columnList.add("name");
//        columnList.add("password");
//        columnList.add("phone");
//        List<List<String>> dataList = new ArrayList<>();
//        for (int i = 0; i < 10; i++) {
//            List<String> s = new ArrayList<>();
//            s.add(i + 1 + "");
//            s.add("张三" + i);
//            s.add("123456" + i);
//            s.add("17607183394" + i);
//            dataList.add(s);
//        }
//        String table = "test";
//        insert(columnList, dataList, table);
        //更新

        //删除

    }

}
